# 粘贴序列,为拼接sql准备,每个变量加了单引号
source("D://KRWO/GX/charging_coefficient/20220113/rpart_source.R",encoding = 'UTF-8')

paste_fun<-function(df){
  pstring<-""
  for(i in 1:length(df)){
    if(i !=1){
      pstring<-paste0(pstring,",") 
    }
      # pstring<-paste0(pstring,"'",df[i],"'")
      pstring<-paste0(pstring,df[i])
  }
  return(pstring)
}
# ''和NA意义相同,填充成一样的
NA_NULL<-function(x){
  if(any(x=='')){
    print(names(x))
    x<-ifelse(x=='',NA,x)
    return(x)
  }else{
    return(x)
  }
}
# 数据获取,2017% + 宝山+ GA外板
get_data<-function(database,conn){
  # 筛选物料号, 逻辑
  sqlend<-"MAT_TRACK_NO like '2017%' and ORDER_NO_CC in (select DISTINCT  ORDER_NO from M1_WE.SU_WE00_MAHT01 where ACCOUNT='1001' and KEY_PRODUCT_DESC_3='GA外板')"
  sqlstr<-paste0("select * from ",database," where MAT_TRACK_NO in ( select distinct MAT_TRACK_NO from M1_WE.SU_WEBG_MBCR01 where " )
  sqlstr<-paste0(sqlstr,sqlend," ) ");
  # print(sqlstr)
  df<-dbGetQuery(conn,sqlstr);
  return(df)
}


# 画图, 柱状图+注释+保存路径, 默认,无调色
# x轴数值型的可以渐变色
barplt_fun = function(df,xvalue,yvalue,save_path,name,xlab=NULL,ylab=NULL){
  library(ggplot2)
  # 柱状图, 渐变色, 标注数量, 添加曲线
   # p=ggplot(data=df,mapping=aes(x=df[,xvalue],y=df[,yvalue],fill=df[,xvalue],group=factor(1)))+
   #  geom_bar(stat="identity")+geom_text(aes(label = df[,xvalue], vjust = -0.8, hjust = 0.5, color =df[,yvalue]), show.legend = TRUE)
 
  p = ggplot(data=df,mapping=aes(x=df[,xvalue],y=df[,yvalue],fill=df[,xvalue]))+  geom_bar(stat="identity")+geom_text(aes(label =  df[,yvalue] , vjust = -0.8, hjust = 0.5, color = df[,xvalue]), show.legend = TRUE)+geom_smooth(size=1)+geom_point(size=2) 
  if(!is.null(xlab)){
    p = p+ xlab(xlab)
  }
  if(!is.null(ylab)){
    p = p+ ylab(ylab)
  }
  
  filename=paste0(save_path,name,'.png')
  print(filename)
  ggsave( p, filename=filename,width = 6,height = 6,dpi=200) 
}


# 饼图，先用2分类的情况
rate_pie_fun<-function(df,x_value,y_value,dir,save_name){
  library('sqldf')
  dt<-df
  sql_string<-paste0("select ",y_value, " as name , sum(",x_value,") as count from dt group by ",y_value, " order by ", y_value)
  dt<-sqldf(sql_string)
  # dt<-merge(dt,label)
  # print(dt)
  library(ggplot2)
    myLabel = as.vector(dt$name)   ## 转成向量，否则图例的标签可能与实际顺序不一致
  myLabel = paste(myLabel, "(", round(dt$count / sum(dt$count) * 100, 2), "%)        ", sep = "")
  p = ggplot(dt, aes(x = "", y = count, fill = name)) + 
    geom_bar(stat = "identity", width = 1) + 
    coord_polar(theta = "y") +
    labs(x = "", y = "", title = "")+
    theme(axis.ticks = element_blank()) +
    theme(legend.title = element_blank(), legend.position = "top")
  scale_fill_discrete(breaks = dt$name, labels = myLabel)
  # p
  #geom_text(label=dt$count,colour = "black", vjust=00)
  
  print( paste0(dir,save_name))
  ggsave( p, filename=paste0(dir,save_name))  
}

# 饼图, 增加备注

rate_pienew_fun<-function(df,x_value,y_value,dir,save_names){
  library('sqldf')
  dt<-df
  sql_string<-paste0("select ",y_value, " as name , sum(",x_value,") as count from dt group by ",y_value, " order by count desc")
  
  dt<-sqldf(sql_string)
  print(dt)
  library(ggplot2)
  print(dt$count/2 + c(0, cumsum(dt$count)[-length(dt$count)]))
  # 排序
  dt = dt[order(dt$count, decreasing = TRUE),]
  dt$count<-round(dt$count,2)
  myLabel = as.vector(dt$name)   
  myLabel = paste(myLabel, "-",dt$count,"\n","(", round(dt$count / sum(dt$count) * 100, 2), "%)", sep = "")   
    p = ggplot(dt, aes(x = "", y = count, fill = name)) + 
        geom_bar(stat = "identity", width = 1) +    
        coord_polar(theta = "y") + 
        labs(x = "", y = "", title = "") + 
        theme(axis.ticks = element_blank()) + 
        theme(legend.title = element_blank(), legend.position = "top") +                 scale_fill_discrete(breaks = dt$name, labels = myLabel)   
                   
                   ggsave( p, filename=paste0(dir,save_names),width = 5,height = 5,dpi=100) 
}



# 饼图, 增加名称
rate_pietitle_fun<-function(df,x_value,y_value,dir,save_names,title){
  library('sqldf')
  dt<-df
  sql_string<-paste0("select ",y_value, " as name , sum(",x_value,") as count from dt group by ",y_value, " order by count desc")
  
  dt<-sqldf(sql_string)
  print(dt)
  library(ggplot2)
  print(dt$count/2 + c(0, cumsum(dt$count)[-length(dt$count)]))
  # 排序
  dt = dt[order(dt$count, decreasing = TRUE),]
  dt$count<-round(dt$count,2)
  myLabel = as.vector(dt$name)   
  myLabel = paste(myLabel, "-",dt$count,"\n","(", round(dt$count / sum(dt$count) * 100, 2), "%)", sep = "")   
  p = ggplot(dt, aes(x = "", y = count, fill = name)) + 
    geom_bar(stat = "identity", width = 1) +    
    coord_polar(theta = "y") + 
    labs(x = "", y = "", title = title) + 
    theme(axis.ticks = element_blank()) + 
    theme(legend.title = element_blank(), legend.position = "top") +                 scale_fill_discrete(breaks = dt$name, labels = myLabel)   
  
  ggsave( p, filename=paste0(dir,save_names)) 
}
# ############
# 缺失值名称
NA_name_fun <- function(df){
  return(names(which(sapply(names(df),function(x) any(is.na(df[,x]))))))
}
# 缺失值名称,缺失比例
# 缺失+''长度
NA_name_rate_fun <- function(df){
    NA_names <- names(which(sapply(names(df),function(x) any(is.na(df[,x])))))
    NA_rate <- sapply(NA_names,function(x) sum(is.na(df[,x]))/length(df[,x]))
    return(NA_rate)
}
# 非缺失的即为唯一值的情况,
NA_name_unique_fun <- function(df,rate=FALSE){
  # 非空即唯一的值的名称
  NA_names <- names(which(sapply(names(df),function(x) length(unique(df[!is.na(df[,x]),x]))==1)))
  # 非空即唯一的值
  NA_value <- sapply(NA_names,function(x) unique(df[!is.na(df[,x]),x]))
  if(rate){
  NA_rate <- sapply(NA_names,function(x) length(df[!is.na(df[,x]),x])/length(df[,x]))
  NA_result <- data.frame(NA_name=names(NA_value),NA_value=NA_value,NA_rate=NA_rate)  
  return(NA_result)
  }else{
    return(NA_value) 
  }
  
  
  # 添加字段唯一值占的比例
  
}
# 替换后的非空即唯一的部分
NA_name_unique_changed_fun <- function(df){
  NA_names <- names(which(sapply(names(df),function(x) length(unique(df[df[,x]!='NA',x]))==1)))
  NA_rate <- sapply(NA_names,function(x) unique(df[df[,x]!='NA',x]))
  return(NA_rate)
}
# 全部是唯一值的部分
NA_all_fun <- function(df){
  return(names(which(sapply(names(df),function(x) all(is.na(df[,x]))))))
}
# 全部缺失
NA_in_fun <- function(df,char){
  return(names(which(sapply(names(df),function(x) any(char%in%df[,x])  ))))
}
# 
# 判断相等,第一个有相等的变量作为关键字, 后面的作为拼接保存, 返回要删除的字段
equal_var_fun <- function(df,name,save_dir,save_name){
  df<-df[,name]
  rm_name<-c() 
  save_frame<-data.frame(key=c('key'),equal_var=c('equal_var'))
  for (i in name) {
    if(!i %in% rm_name){
      remain_name <- setdiff(name,c(rm_name,i))
      rm_namestring <- names(which(sapply(remain_name,function(x) isTRUE(all.equal(df[,x],df[,i] )) ))  )
      if(length(rm_namestring)>=1){
        save_frame<-rbind(save_frame,data.frame(key=c(i),equal_var=paste_fun(rm_namestring)))
        rm_name <- union(rm_name,rm_namestring) 
      }
    }
   
    
    
  }
  write.csv(save_frame[-1,],paste0(save_dir,save_name,".csv"),row.names = F)
   return(rm_name) 

}



# ##################################
# 因子转化成字符型
char_var <- function(df){
  to_char<-names(df[,sapply(df,function(x) is.factor(x))])
  df[,to_char] <- lapply(df[,to_char],as.character)
  return(df)
}


#####################################
# 字符型画图


#######################
# 判断相等
# equal_var_fun <- function(df,temp_dir){
#   var_names<-names(df)
#   len<-length(var_names)
#   skip_var <- c()
#   equal_point <- 0
#   return_df<-c()
#   for(i in 1:(len-1)){
#     equal_point <- 0
#     if(!i%in%skip_var){
#       save_var<-var_names[i]
#       for(j in (i+1):len){
#         if(!j%in%skip_var){
#           if(all(df[,i]==df[,j])){
#             skip_var <- c(skip_var,j)
#             save_var<-c(save_var,var_names[j])
#             equal_point <- 1
#             return_df<-c(return_df,var_names[j])
#           }
#         }
#       }
#     }
#     
#     if(equal_point==1){
#       write.csv(save_var,paste0(temp_dir,var_names[i],'.csv'),row.names = F)
#     }
#     print(return_df)
#   }
#   return(return_df)
# }

######################
# 判断唯一值的列
unique_var_fun<-function(df){
  unique_var<-c()
  unique_var_val<-c()
  for(i in names(df)){
    if(length(unique(as.character(df[,i]) ))==1){
      unique_var<-c(unique_var,i)
      unique_var_val<-c(unique_var_val,unique(as.character(df[,i])))
    }
  }
  result<-data.frame(var_names=unique_var,var_val=unique_var_val)
  return(result)
}


######################
# 堆叠条形图,ggplot画图, 问题: 如何根据分类个数调整图像的大小, 还要添加比例线图
stacked_barnew_fun<-function(df,x_value,y_value,f_value,dir,names=NULL,cols=NULL,limits=NULL,x_factor=NULL,f_factor=NULL,fvalue_rate=NULL,xangle=0){
  library(ggplot2)
  library(sqldf)
  sql_string<-paste0("select ",x_value, ", ", f_value, ", sum(", y_value,") as ", y_value, " from df group by ",x_value,", ",f_value)
  df_temp<-sqldf(sql_string)
  df_temp[,y_value]<-round(df_temp[,y_value],2)
  if(!is.null(x_factor)){
    df_temp[,x_value]<-factor(df_temp[,x_value],levels=x_factor,ordered=TRUE)
  }
  if(!is.null(f_factor)){
    df_temp[,f_value]<-factor(df_temp[,f_value],levels= f_factor,ordered=TRUE)
  }
  
  df_temp[,"lab"]<-0
  df_temp[,"lab1"]<-0
  df_temp[,"labrate"]<-0
  df_temp[,"valrate"]<-0
  # 全部的量
  al_wt<-sum(df_temp[,y_value])
  # 改成各部分改钢占的量
  al_wt<-sum(df_temp[df_temp[,f_value]==fvalue_rate,y_value])
  # print(al_wt)
  for(i in df_temp[,x_value]){
    temp<-c()
    # print(i)
    for(j in f_factor){
      
      if(j%in%df_temp[df_temp[,x_value]==i,f_value]){
        
        #print(df_temp[df_temp[,x_value]==i&df_temp[,f_value]==j,y_value])
        temp<-c(temp,df_temp[df_temp[,x_value]==i&df_temp[,f_value]==j,y_value])
      }
      
    }
    df_temp[df_temp[,x_value]==i,"lab"]<-cumsum(rev(temp))
    df_temp[df_temp[,x_value]==i,"lab1"]<-round(c(df_temp[df_temp[,x_value]==i,"lab"][1],diff(df_temp[df_temp[,x_value]==i,"lab"])),2)
    df_temp[df_temp[,x_value]==i,"lab"]<-c(0,df_temp[df_temp[,x_value]==i,"lab"][1:(length(df_temp[df_temp[,x_value]==i,"lab"])-1)])[1:length(df_temp[df_temp[,x_value]==i,"lab"])]+df_temp[df_temp[,x_value]==i,"lab1"]/2
    #+df_temp[df_temp[,x_value]==i,"lab1"]/2
    df_temp[df_temp[,x_value]==i,"valrate"]<-round(sum(df_temp[df_temp[,x_value]==i,y_value]),4)
    # 改成占总体改钢的量
    if(fvalue_rate%in%df_temp[df_temp[,x_value]==i,f_value]){
      df_temp[df_temp[,x_value]==i,"labrate"]<-round(sum(df_temp[(df_temp[,x_value]==i)&(df_temp[,f_value]==fvalue_rate),y_value])/al_wt,4) 
        
     
    }else{
      df_temp[df_temp[,x_value]==i,"labrate"]<-0
    }
 
    # 小卷比例画图
  }
  #print(1)
  #df_temp[df_temp[,"labrate"]<0.02,"valrate"]<-df_temp[df_temp[,"labrate"]<0.02,"valrate"]*1.5
  df_temp[,"labrate"]<-paste0(df_temp[,"labrate"]*100,"%")
  #print(3)
  # View(df_temp)
  #df_temp<-df_temp[order(df_temp[,f_value]),]
  if(!is.null(cols)){
  
    p=ggplot(df_temp,aes(x = factor(df_temp[,x_value]),y = df_temp[,y_value],fill = df_temp[,f_value]))+
      geom_bar(stat = "identity",  position=position_stack(.9) )  +theme(axis.text.x = element_text(  vjust = 0.5, hjust = 0.5, angle = xangle))+geom_text(aes(y=df_temp[,"valrate"]*1.02,label=df_temp[,"labrate"]),colour = "red", vjust = 0)+
      geom_text(aes(y=df_temp[,"lab"]*0.96 ,label=df_temp[,"lab1"]),colour = "black", vjust=00) 
    p<-p+scale_fill_manual(values = cols,  limits=limits)
    # geom_text(aes(y=df_temp[,"lab"]*0.96,label=df_temp[,"lab1"]),colour = "black", vjust=00)
    #+geom_text(aes(y=df_temp[,"valrate"]*1.02,label=df_temp[,"labrate"]),colour = "red", vjust = 0, hjust = 0.2)
  }else{
    p=ggplot(df_temp,aes(x = factor(df_temp[order(df_temp[,f_value]),x_value]),y = df_temp[order(df_temp[,f_value]),y_value],fill = df_temp[,f_value]))+
      geom_bar(stat = "identity",position = "stack")+geom_text(label=df_temp[,y_value],colour = "black", vjust=00) +  theme(axis.text.x = element_text(  vjust = 0.5, hjust = 0.5, angle = xangle))
  }
  
  if(is.null(names)){
    filename=paste0(dir,x_value,'.png') 
  }else{
    filename=paste0(dir,names,'.png')
  }
  
  ggsave( p, filename=filename,width = 12,height = 12,dpi=100) 
}

# 将上面堆叠条形图中比例修改成，1.输出csv文件2.比例为本组比例
stacked_bargrouprate_fun<-function(df,x_value,y_value,f_value,dir,names=NULL,cols=NULL,limits=NULL,x_factor=NULL,f_factor=NULL,fvalue_rate=NULL,xangle=0){
  library(ggplot2)
  library(sqldf)
  sql_string<-paste0("select ",x_value, ", ", f_value, ", sum(", y_value,") as ", y_value, " from df group by ",x_value,", ",f_value)
  df_temp<-sqldf(sql_string)
  df_temp[,y_value]<-round(df_temp[,y_value],2)
  if(!is.null(x_factor)){
    df_temp[,x_value]<-factor(df_temp[,x_value],levels=x_factor,ordered=TRUE)
  }
  if(!is.null(f_factor)){
    df_temp[,f_value]<-factor(df_temp[,f_value],levels= f_factor,ordered=TRUE)
  }
  
  df_temp[,"lab"]<-0
  df_temp[,"lab1"]<-0
  df_temp[,"labrate"]<-0
  df_temp[,"valrate"]<-0
  # 本组比例
  df_temp[,"grouprate"] = 0
  # 全部的量
  al_wt<-sum(df_temp[,y_value])
  # 改成各部分改钢占的量
  al_wt<-sum(df_temp[df_temp[,f_value]==fvalue_rate,y_value])
  # print(al_wt)
  for(i in df_temp[,x_value]){
    temp<-c()
    # print(i)
    for(j in f_factor){
      
      if(j%in%df_temp[df_temp[,x_value]==i,f_value]){
        
        #print(df_temp[df_temp[,x_value]==i&df_temp[,f_value]==j,y_value])
        temp<-c(temp,df_temp[df_temp[,x_value]==i&df_temp[,f_value]==j,y_value])
      }
      
    }
    df_temp[df_temp[,x_value]==i,"lab"]<-cumsum(rev(temp))
    df_temp[df_temp[,x_value]==i,"lab1"]<-round(c(df_temp[df_temp[,x_value]==i,"lab"][1],diff(df_temp[df_temp[,x_value]==i,"lab"])),2)
    df_temp[df_temp[,x_value]==i,"lab"]<-c(0,df_temp[df_temp[,x_value]==i,"lab"][1:(length(df_temp[df_temp[,x_value]==i,"lab"])-1)])[1:length(df_temp[df_temp[,x_value]==i,"lab"])]+df_temp[df_temp[,x_value]==i,"lab1"]/2
    #+df_temp[df_temp[,x_value]==i,"lab1"]/2
    df_temp[df_temp[,x_value]==i,"valrate"]<-round(sum(df_temp[df_temp[,x_value]==i,y_value]),4)
    # 改成占本组总量的数值
    if(fvalue_rate%in%df_temp[df_temp[,x_value]==i,f_value]){
      df_temp[df_temp[,x_value]==i,"labrate"]<-round(sum(df_temp[(df_temp[,x_value]==i)&(df_temp[,f_value]==fvalue_rate),y_value])/al_wt,4) 
     }else{
      df_temp[df_temp[,x_value]==i,"labrate"]<-0
    }
    
    if(fvalue_rate%in%df_temp[df_temp[,x_value]==i,f_value]){
      df_temp[df_temp[,x_value]==i,"labrate"]<-round(sum(df_temp[(df_temp[,x_value]==i)&(df_temp[,f_value]==fvalue_rate),y_value])/al_wt,4) 
    }else{
      df_temp[df_temp[,x_value]==i,"labrate"]<-0
    }  
    if(fvalue_rate%in%df_temp[df_temp[,x_value]==i,f_value]){
      df_temp[df_temp[,x_value]==i,"grouprate"]<-round(sum(df_temp[(df_temp[,x_value]==i)&(df_temp[,f_value]==fvalue_rate),y_value])/sum(df_temp[df_temp[,x_value]==i,y_value]),4) 
    }else{
      df_temp[df_temp[,x_value]==i,"grouprate"]<-0
    }
  }

  df_temp[,"labrate"]<-paste0(df_temp[,"labrate"]*100,"%")
  df_temp[,"grouprate"]<-paste0(df_temp[,"grouprate"]*100,"%")
  # 保存数据
  write.csv(unique(df_temp[,c(x_value,y_value,f_value,"labrate","grouprate")]),paste0(dir,names,'.csv'),row.names = F)
  # 为画图方便
  df_temp[,"labrate"]<-df_temp[,"grouprate"]

  if(!is.null(cols)){
    
    p=ggplot(df_temp,aes(x = factor(df_temp[,x_value]),y = df_temp[,y_value],fill = df_temp[,f_value]))+
      geom_bar(stat = "identity",  position=position_stack(.9) )  +theme(axis.text.x = element_text(  vjust = 0.5, hjust = 0.5, angle = xangle))+geom_text(aes(y=df_temp[,"valrate"]*1.02,label=df_temp[,"labrate"]),colour = "red", vjust = 0)+
      geom_text(aes(y=df_temp[,"lab"]*0.96 ,label=df_temp[,"lab1"]),colour = "black", vjust=00) 
    p<-p+scale_fill_manual(values = cols,  limits=limits)
    # geom_text(aes(y=df_temp[,"lab"]*0.96,label=df_temp[,"lab1"]),colour = "black", vjust=00)
    #+geom_text(aes(y=df_temp[,"valrate"]*1.02,label=df_temp[,"labrate"]),colour = "red", vjust = 0, hjust = 0.2)
  }else{
    p=ggplot(df_temp,aes(x = factor(df_temp[order(df_temp[,f_value]),x_value]),y = df_temp[order(df_temp[,f_value]),y_value],fill = df_temp[,f_value]))+
      geom_bar(stat = "identity",position = "stack")+geom_text(label=df_temp[,y_value],colour = "black", vjust=00) +  theme(axis.text.x = element_text(  vjust = 0.5, hjust = 0.5, angle = xangle))
  }
  
  if(is.null(names)){
    filename=paste0(dir,x_value,'.png') 
  }else{
    filename=paste0(dir,names,'.png')
  }
  
  ggsave( p, filename=filename,width = 12,height = 12,dpi=100) 
}


###################
list.rules.rpart <- function(model,save_name,dir_name)
{
  if (!inherits(model, "rpart")) stop("Not a legitimate rpart tree")
  #
  # Get some information.
  #
    if(any( grepl("/",save_name,fixed = T))){
  save_name<-str_replace_all(save_name,"\\/","")
  }
  savefile=paste0(dir_name,save_name,'.txt')
  if(model$method=='class'){
    frm     <- model$frame
    names   <- row.names(frm)
    ylevels <- attr(model, "ylevels")
    ds.size <- model$frame[1,]$n
    #
    # Print each leaf node as a rule.
    #
    for (i in 1:nrow(frm))
    {
      if (frm[i,1] == "<leaf>")
      {
        # The following [,5] is hardwired - needs work!
        if(file.exists(savefile)){
          cat("\n",file=savefile,append = TRUE)
        }else{
          cat("\n",file=savefile) 
        }
        
        cat(sprintf(" Rule number: %s ", names[i]),file=savefile,append = TRUE)
        cat(sprintf("[yval=%s cover=%d (%.0f%%) prob=%0.2f]\n",
                    ylevels[frm[i,]$yval], frm[i,]$n,
                    round(100*frm[i,]$n/ds.size), frm[i,]$yval2[,5]),file=savefile,append = TRUE)
        pth <- path.rpart(model, nodes=as.numeric(names[i]), print.it=FALSE)
        cat(sprintf("   %s\n", unlist(pth)[-1]), sep="",file=savefile,append = TRUE)
      }
    }
  }else if(model$method=="anova"){
    frm     <- model$frame
    names   <- row.names(frm)
    # ylevels <- attr(model, "ylevels")
    ds.size <- model$frame[1,]$n
    #
    # Print each leaf node as a rule.
    #
    for (i in 1:nrow(frm))
    {
      if (frm[i,1] == "<leaf>")
      {
        if(file.exists(savefile)){
          cat("\n",file=savefile,append = TRUE)
        }else{
          cat("\n",file=savefile) 
        }
        
        cat(sprintf(" Rule number: %s ", names[i]),file=savefile,append = TRUE)
        cat(sprintf("[yval=%s cover=%d (%.0f%%) ]\n",
                    frm[i,]$yval, frm[i,]$n,
                    round(100*frm[i,]$n/ds.size)),file=savefile,append = TRUE)
        pth <- path.rpart(model, nodes=as.numeric(names[i]), print.it=FALSE)
        cat(sprintf("   %s\n", unlist(pth)[-1]), sep="",file=savefile,append = TRUE)
      }
    }
    
    
    
  }
  
}
# demo
# library(rpart)
# # 决策
# fit <- rpart(Kyphosis ~ Age + Number + Start, data = kyphosis)
# plot(fit)
# text(fit, use.n = TRUE)
# list.rules.rpart(fit)
# list.rules.rpart(fit,save_name = 'fit',dir_name = 'D:/')
# # 回归
# fit1 <- rpart( Age ~  Kyphosis+ Number + Start, data = kyphosis)
# plot(fit1)
# text(fit1, use.n = TRUE)
# list.rules.rpart(fit1)
# list.rules.rpart(fit1,save_name = 'fit',dir_name = 'D:/')

# 提取决策规则, 形成csv文件, 并且画图
decision_path<-function(tree_model,df,save_name,save_path){
  library("sqldf")
  frm     <- tree_model$frame
  names   <- row.names(frm)
  ds.size <- tree_model$frame[1,]$n
  var_names<-setdiff(as.character(tree_model$frame[,"var"]),"<leaf>")  
  # 含有空格的
  sapce_vars<-var_names[which(grepl(" ",var_names))]
  # 含有-号的
  sapce_vars<-c(sapce_vars,var_names[which(grepl("-",var_names))])
  sapce_vars<-c(sapce_vars,var_names[which(grepl("+",var_names,fixed = T))])
  sapce_vars<-c(sapce_vars,var_names[which(grepl("/",var_names,fixed = T))])
  sapce_vars<-c(sapce_vars,var_names[which(grepl(".",var_names,fixed = T))])
  rules_df<-data.frame(rule_no=NA, rate=0, IN_WT_SUM=0, OUT_WT_SUM=0)
  for(i in var_names){
    rules_df[,i]<-NA
  }
  rules_df[,"sql"]<-NA
  # 决策路径

  # 规则编号 rule_no 初始值1
  rule_no<-1
  # 可追溯规则的行, 决策路径
  
  frame_temp<-tree_model$frame
  # ID_IN_NODE<-data.frame(nodes=row.names(frame_temp),nodeindex= c(1:length(row.names(frame_temp))))
  frame_temp<-frame_temp[frame_temp$var=="<leaf>", c("var","n")]
  nodes<-row.names(frame_temp)
  # ID_IN_NODE<-ID_IN_NODE[ID_IN_NODE$nodes%in%as.numeric(nodes),]
  # 判断每个节点在哪个里面
  # id_where= data.frame( idindex=names(tree_model$where),nodeindex=tree_model$where)
  nodes<-as.numeric(nodes)
  # 一条条规则
  if(nodes !=1){
  for( node in nodes ){
  # 改写函数
    rule_temp <- path.rpart_digits(tree_model, nodes= node, print.it=FALSE,digits=10)
    rule_temp<-unlist(rule_temp)[-1]
    # sql语句先写死IN_MAT_WT_1 改成 IN_WT_SUM
    sql_string<-"select sum(IN_WT_SUM) as IN_WT_SUM, sum(OUT_WT_SUM) as OUT_WT_SUM from  df where "
    sql_tag<-1
    for(vars in var_names){
      if(any(grepl(vars,rule_temp,fixed = T))){
        # 一个可能出现多次
        var_times<-sum(grepl(vars,rule_temp,fixed = T)) 
        rule_var<-rule_temp[grepl(vars,rule_temp,fixed = T)]
        # 只出现一次
        if(is.na( rules_df[rule_no,"rule_no"])){
          rules_df[rule_no,"rule_no"]<-paste0("Rule number ", rule_no)
        }
        for(var_time in 1:var_times){
          if(is.na(rules_df[rule_no,vars])){
            rules_df[rule_no,vars]<-rule_var[var_time]
          }else{
            rules_df[rule_no,vars]<-paste0(rules_df[rule_no,vars]," & ",rule_var[var_time])  
          }
          
          if(sql_tag){
            
            sql_string<-paste(sql_string, rule_var[var_time],sep = ' ')
            sql_tag<-0
          }else{
            sql_string<-paste(sql_string, rule_var[var_time],sep = ' and ')
          }
          
        }
        
      }
      rules_df[rule_no,"sql"]<-sql_string
      
    } 
    #########################
	library(stringr)
	# 修改'<'符号问题,
  sql_string<-gsub("="," =('",sql_string)
	# print(sql_string)
	less_sign<-paste0(var_names,"< ")
	is_less_sign<-str_detect(sql_string, fixed(paste0(var_names,"<")))
	if(any(is_less_sign) ){
	less_sign<-less_sign[is_less_sign]
	for(less_length in less_sign){
	sql_string<-gsub(less_length, paste0(less_length, "('"),sql_string,fixed = T)
	}
	}
	rep_tag<-grepl("\\('\\('",sql_string)
	while(rep_tag){
	  sql_string<-str_replace_all(sql_string,"\\('\\('","\\('")
	  rep_tag<-grepl("\\('\\('",sql_string)
	}
	#

# 	tag_var<-c()
# for(less_length in less_sign){
#
#   flag<-0
#   if(any(grepl(less_length,tag_var) ) ){
#     flag<-any(grepl(less_length,tag_var) )
#
#   }else{
#     if(length(tag_var)!=0){
#       for(tvar in tag_var){
#         if(any(grepl(tvar,less_length) ) ){
#           flag<-any(grepl(less_length,tag_var) )
#           }
#       }
#     }  }
#
#   if(!flag){
#     sql_string<-gsub(less_length, paste0(less_length, "('"),sql_string)
#   }
#   tag_var<-c(tag_var,less_length)
# }
#
#
# 	}



   # sql_string<-gsub("<"," <('",sql_string)
    sql_string<-gsub(" and","') and",sql_string)
    if( grepl('=',sql_string)){
      # =,<,>=
      sql_string<-gsub("="," in ",sql_string)
      sql_string<-gsub(">  in"," >= ",sql_string)
    }
    #library(stringr)
    if(str_count(sql_string,",")>1){
      sql_string<- gsub(",", "','", sql_string)
      sql_string<- gsub("IN_WT_SUM','", "IN_WT_SUM,", sql_string)
    }
    sql_string<-paste0(sql_string,"')")
    # df_temp<-sqldf(sql_string)
    
    # 含有空格的
    if(length(sapce_vars)!=0){
      for(space_var in sapce_vars){
        sql_string<-gsub(space_var, paste0("[",space_var, "]"),sql_string,fixed = T)
      }
    }
    rep_tag<-grepl("\\[\\[",sql_string)
	while(rep_tag){
	  sql_string<-str_replace_all(sql_string,"\\[\\[","\\[")
	   sql_string<-str_replace_all(sql_string,"\\]\\]","\\]")
	  rep_tag<-grepl("\\[\\[",sql_string)
	}
    rep_tag<-grepl("\\(' ",sql_string)
    while(rep_tag){
      sql_string<-str_replace_all(sql_string,"\\(' ","\\('")
      
      rep_tag<-grepl("\\(' ",sql_string)
    }
    
    rep_tag<-grepl(" \\)'",sql_string)
    while(rep_tag){
      sql_string<-str_replace_all(sql_string," \\)'","\\)'")
      
      rep_tag<-grepl(" \\)'",sql_string)
    }
    
    
	try_df<-try(sqldf(sql_string))

	if(class(try_df)=="try-error"){

	print('error')
	print(sql_string)
	print(node)
	print('end')
	}else{
	df_temp <- try_df
	}



    rules_df[rule_no,"IN_WT_SUM"]<-df_temp[,"IN_WT_SUM"]
    rules_df[rule_no,"OUT_WT_SUM"]<-df_temp[,"OUT_WT_SUM"]
    rule_no<-rule_no+1

    ############################
    # 
    # nodeindex<-ID_IN_NODE[ID_IN_NODE$nodes==node,"nodeindex"]
    # idindex<-id_where[id_where$nodeindex==nodeindex,"idindex"]
    # rules_df[rule_no,"IN_WT_SUM"]<-sum(df[idindex,"IN_MAT_WT_1"])
    # rules_df[rule_no,"OUT_WT_SUM"]<-sum(df[idindex,"OUT_WT_SUM"])
    # rule_no<-rule_no+1
    # 
    }}
  rules_df$rate<-rules_df[,"OUT_WT_SUM"]/ rules_df[,"IN_WT_SUM"]
  rules_df[,"OUT_WT_SUM"]<-round(rules_df[,"OUT_WT_SUM"],2)
  rules_df[,"IN_WT_SUM"]<-round(rules_df[,"IN_WT_SUM"],2)
  if(any( grepl("/",save_name,fixed = T))){
  save_name<-str_replace_all(save_name,"\\/","")
  }
  write.csv(rules_df,paste0(save_path,save_name,".csv"))
  
  return(rules_df)
  }

transform_str<-function(string,tablename_un,len){
  if(any(grepl('&',string))){
    string_part=strsplit(gsub(" & ","&&",gsub("< ",'&&',gsub('>=','&&',string))),"&&")
    if(string_part[[1]][1]==string_part[[1]][3]){
      string_new=gsub(paste(' & ',tablename_un[len],'__',sep=""),' and ',string)
    }
    else{
      string_new=transform_str(strsplit(string," & ")[[1]][1],tablename_un,len)
    }
  }
  else{
    string__number<-length(strsplit(string, "__")[[1]])
    string_part<-strsplit(string, "__")
    if(string__number==3){
      if(any(grepl('RANGE',string_part[[1]][2]))) {
        if(any(grepl('< 0.5',string_part[[1]][3]))) {
          ran <- gsub('< 0.5','',string_part[[1]][3])
          rans <- strsplit(ran, '_')
          string_part_1 <- paste(gsub('RANGE','MIN',string_part[[1]][2]),'!=\'',rans[[1]][1],'\'',sep="")
          string_part_2 <- paste(gsub('RANGE','MAX',string_part[[1]][2]),'!=\'',rans[[1]][2],'\'',sep="")
          string_part_fin <- paste(string_part[[1]][1],'__','(',string_part_1,' or ',string_part_2,')',sep="")
        }
        else {
          ran <- gsub('>=0.5','',string_part[[1]][3])
          rans <- strsplit(ran, '_')
          string_part_1 <- paste(gsub('RANGE','MIN',string_part[[1]][2]),'=\'',rans[[1]][1],'\'',sep="")
          string_part_2 <- paste(gsub('RANGE','MAX',string_part[[1]][2]),'=\'',rans[[1]][2],'\'',sep="")
          string_part_fin <- paste(string_part[[1]][1],'__','(',string_part_1,' and ',string_part_2,')',sep="")
        }
      }
      else{
        if(any(grepl('>=0.5',string))){
          string<-sub(">=0.5","",string,fixed = T)
          string_part<-strsplit(string, "__")
          string_new<-sub(paste('__',string_part[[1]][3],sep = ""),paste("='",string_part[[1]][3],"'",sep = ""),string,fixed = T)
        }
        else if(any(grepl('< 0.5',string))){
          string<-sub("< 0.5","",string,fixed = T)
          string_part<-strsplit(string, "__")
          string_new<-sub(paste('__',string_part[[1]][3],sep = ""),paste("!='",string_part[[1]][3],"'",sep = ""),string,fixed = T)
        }
        else{string_new<-string}
      }
    }
    else if(string__number==4){
      if(any(grepl('>=0.5',string))){
        string<-sub(">=0.5","",string,fixed = T)
        string_part<-strsplit(string, "__")
        string_new<-sub(paste(string_part[[1]][2],'__',string_part[[1]][3],'__',string_part[[1]][4],sep = ""),paste("substr(",string_part[[1]][2],",",strsplit(string_part[[1]][3],"_")[[1]][1],",",as.numeric(strsplit(string_part[[1]][3],"_")[[1]][2])-as.numeric(strsplit(string_part[[1]][3],"_")[[1]][1])+1,")='",string_part[[1]][4],"'",sep = ""),string,fixed = T)
      }
      else{
        string<-sub("< 0.5","",string,fixed = T)
        string_part<-strsplit(string, "__")
        string_new<-sub(paste(string_part[[1]][2],'__',string_part[[1]][3],'__',string_part[[1]][4],sep = ""),paste("substr(",string_part[[1]][2],",",strsplit(string_part[[1]][3],"_")[[1]][1],",",as.numeric(strsplit(string_part[[1]][3],"_")[[1]][2])-as.numeric(strsplit(string_part[[1]][3],"_")[[1]][1])+1,")!='",string_part[[1]][4],"'",sep = ""),string,fixed = T)
      }
    }
    else{
      string_new<-string
    }
  }
}

